""" 用于操作数据库的工具类py文件 """
import sqlite3 as sqlite
import os
class SQLHLPER(object):
    DBENGIN=''
    CONNECTSTRING=os.path.dirname(__file__)+'/db/MRDB.db'
    CONN=''
    CUR=''

    @classmethod
    def executescript(self,sqlstr=''):
        conn=sqlite.connect(SQLHLPER.CONNECTSTRING,timeout=10)        
        cur=conn.cursor()
        try:
            cur.executescript(sqlstr)
            conn.commit()
            return conn.total_changes
        except Exception as e:
            # print(e)
            conn.rollback()
            raise(e)
        finally:
            cur.close()
            conn.close()


    @classmethod   
    def execute(self,sqlstr='',*parms):
        conn=sqlite.connect(SQLHLPER.CONNECTSTRING,timeout=10)        
        cur=conn.cursor()
        try:
            cur.execute(sqlstr,parms)
            conn.commit()
            return conn.total_changes
        except Exception as e:
            # print(e)
            conn.rollback()
            raise(e)
        finally:
            cur.close()
            conn.close()
    

    @classmethod    
    def selectone(self,sqlstr='',*parms,**keys):
        conn=sqlite.connect(SQLHLPER.CONNECTSTRING,timeout=10)        
        cur=conn.cursor()
        try:
            rs=cur.execute(sqlstr,parms)
            # conn.commit()
            return rs.fetchone()
        except Exception as e:
            # print(e)
            conn.rollback()
            raise(e)
        finally:
            cur.close()
            conn.close()


    @classmethod    
    def selectmany(self,sqlstr='',rows=10,*parms):
        conn=sqlite.connect(SQLHLPER.CONNECTSTRING,timeout=10)        
        cur=conn.cursor()
        try:
            rs=cur.execute(sqlstr,parms)
            # conn.commit()
            return rs.fetchmany(rows)
        except Exception as e:
            # print(e)
            conn.rollback()
            raise(e)
        finally:
            cur.close()
            conn.close()



    @classmethod    
    def selectall(self,sqlstr='',**keywards):
        conn=sqlite.connect(SQLHLPER.CONNECTSTRING,timeout=10)        
        cur=conn.cursor()
        try:
            rs=cur.execute(sqlstr,keywards)
            # conn.commit()
            return rs.fetchall()
        except Exception as e:
            # print(e)
            conn.rollback()
            raise(e)
        finally:
            cur.close()
            conn.close()


""" 测试上面的类 """
if __name__ == '__main__':
    # parm=[('123459','exmp3'),('123460','exmp4')]
    # str1=" insert into mrinfo(hospital_num,pat_name,in_date,dis_date,filing_time) values('{0:s}','{1:s}','2020-01-01','2020-04-01','2020-04-05 16:00:00');".format(*parm[0]) 
    # str2="insert into mrinfo(hospital_num,pat_name,in_date,dis_date,filing_time) values('{0:s}','{1:s}','2020-01-01','2020-04-01','2020-04-05 17:00:00');".format(*parm[1])
    # str=str1+str2
    # # print(str)
    # rs=SQLHLPER.executescript(str)
    # print(rs)
    sqlstr='select is_on,mr_num,HOSPITAL_NUM,PAT_NAME,ID_NUM,IN_DATE,DIS_DATE,DIS_WAY,DIS_GROUP,DOCTOR,ADDR,ID from mrinfo	where mr_num=:keyword or HOSPITAL_NUM=:keyword or PAT_NAME LIKE :keyword2 or ID_NUM LIKE :keyword2 ORDER BY PAT_NAME,FILING_TIME DESC'
    rs=SQLHLPER.selectall(sqlstr,**{"keyword":"12345",'keyword2':'%12345%'})
    print(rs)




